from django.db.models import Q
from django.shortcuts import render
from django.http import JsonResponse
import json
# Create your views here.
from student.models import Student,Userinfo
import openpyxl

import uuid
import hashlib
import os
# 导入Setting
from django.conf import settings


def index(request):
    try:
        student_obj=Student.objects.all().values()
        students=list(student_obj)
        #print(students)
        return JsonResponse({"code":1,"data":students})
    except Exception as e:
        return JsonResponse({"code":0,"msg":str(e)})
    # return render(request,'index.html')

def query_students(request):   #查询学生信息接口

    data=json.loads(request.body.decode('utf-8'))
    #print(data)
    try:
        obj_students = Student.objects.filter(Q(sno__icontains=data['inputstr']) | Q(sname__icontains=data['inputstr'])|
                                                Q(gender__icontains=data['inputstr']) | Q(mobile__icontains=data['inputstr'])|
                                              Q(email__icontains=data['inputstr']) | Q(address__icontains=data['inputstr'])).values()
        students = list(obj_students)
        #print(students)
        return JsonResponse({"code":1,"data":students})
    except Exception as e:
        return JsonResponse({"code":0,"msg":"获取学生信息异常"+str(e)})

def is_exist_sno(request):
    data=json.loads(request.body.decode("utf-8"))
    try:
        obj_student=Student.objects.filter(sno=data['sno'])
        if obj_student.count()==0:
            return JsonResponse({'code':1,'exist':False})
        else:
            return JsonResponse({'code':1,'exist':True})
    except Exception as e:
        return JsonResponse({'code':0,'msg':'校验学号失败，具体原因：'+str(e)})

def add_student(request):
    data=json.loads(request.body.decode("utf-8"))
    print("****")
    print(data)
    print(type(data))
    try:
        student = Student(sno=data["sno"], sname=data["sname"], gender=data["gender"], birthday=data["birthday"],
                          mobile=data["mobile"], email=data["email"], address=data["address"],image=data["image"])
        student.save()

        students_get=Student.objects.all().values()
        students=list(students_get)
        return  JsonResponse({"code":1,"data":students})

    except Exception as e:
        return JsonResponse({"code":1,"msg":"添加学生信息失败"+str(e)})

def userinfo(request):
    try:
        Userinfos=Userinfo.objects.all().values()
        userinfos=list(Userinfos)
        print(userinfos)
        return JsonResponse({"code":1,"data":userinfos})
    except Exception as e:
        return JsonResponse({"code":0,"msg":e})

def add_new_userinfo(request):
    data=json.loads(request.body.decode("utf-8"))
    print("guo20201017")
    print(data)
    print(type(data))
    try:
        newuser=Userinfo(user_name=data["uname"],pass_word=data["pwd"])
        print(data["uname"])
        newuser.save()
        all_newuser=Userinfo.objects.all().values()
        print(1111)
        print(all_newuser)
        all_newusers=list(all_newuser)
        return JsonResponse({"code":1,"data":all_newusers})
    except Exception as e:
        return ({"code":0,"msg":str(e)})


def update_student(request):
    '''获取前端传递过来的信息'''
    try:
        data = json.loads(request.body.decode("utf-8"))
        obj_student=Student.objects.get(sno=data['sno'])
        obj_student.sno = data['sno']
        obj_student.sname=data['sname']
        obj_student.gender=data['gender']
        obj_student.birthday=data['birthday']
        obj_student.mobile=data['mobile']
        obj_student.address=data['address']
        obj_student.image=data['image']
        obj_student.save()
        '''再次获取所有学生信息'''
        obj_students=Student.objects.all().values()
        '''把外层容器转换成list'''
        students=list(obj_students)
        return JsonResponse({'code':1,'data':students})
    except Exception as e:
        print(e)
        return JsonResponse({'code':0,"msg":"修改学生信息保存到数据库异常："+str(e)})

def delete_student(request):
    print(request.body.decode("utf-8"))
    data=json.loads(request.body.decode("utf-8"))
    print(data)
    try:
        print("000000")
        print(data)
        obj_student=Student.objects.get(sno=data['sno'])
        print("***&&&&")
        print(obj_student)
        obj_student.delete()

        obj_students=Student.objects.all().values()
        students=list(obj_students)
        return JsonResponse({"code":1,"data":students})
    except Exception as e:
        return JsonResponse({"code":0,"msg":"删除信息错误"+str(e)})

def delete_students(request):
    obj_students=json.loads(request.body.decode("utf-8"))
    print(obj_students)
    '''
    obj_students的值为：{'studentt': [{'id': 23, 'sno': '95888', 'sname': 'sdf', 'gender': '男', 'birthday': '2020-08-11', 'mobile': '12121212', 'email':
    '1@12', 'address': 'sdfsdgsdfs '}, {'id': 25, 'sno': '95999', 'sname': '阿萨德', 'gender': '男', 'birthday': '2020-08-02', 'mobile
    ': '12222', 'email': '1222', 'address': 'sdf水电费水电费水电费水电费的'}]}
    '''
    try:
        for one_student in obj_students['studentt']:
            obj_student=Student.objects.get(sno=one_student['sno'])
            obj_student.delete()
        obj_students=Student.objects.all().values()
        students=list(obj_students)
        return JsonResponse({'code':1,'data':students})
    except Exception as e:
        return JsonResponse({'code':0,'msg':'批量删除学生信息失败，具体原因：'+str(e)})

def upload(request):
    """接收上传的文件"""
    rev_file = request.FILES.get('avatar')
    # 判断，是否有文件
    print("&&&&&&")
    #print(rev_file)
    print(rev_file.name)
    print(type(rev_file))
    print(type(rev_file.name))
    if not rev_file:
        return JsonResponse({'code':0, 'msg':'图片不存在！'})
    # 获得一个唯一的名字： uuid +hash
    new_name = get_random_str()
    # 准备写入的URL
    file_path = os.path.join(settings.MEDIA_ROOT, new_name + os.path.splitext(rev_file.name)[1] )
    # 开始写入到本次磁盘
    print("0000000000")
    print(file_path)
    print(os.path.splitext(rev_file.name))
    try:
        f = open(file_path,'wb')
        # 多次写入
        for i in rev_file.chunks():
            print("guo")
            #print(i)
            print(type(i))
            f.write(i)
        # 要关闭
        f.close()
        # 返回
        return JsonResponse({'code': 1, 'name': new_name + os.path.splitext(rev_file.name)[1]})

    except Exception as e:
        return JsonResponse({'code':0, 'msg':str(e)})

def import_students_excel(request):
    """从Excel批量导入学生信息"""
    # ========1. 接收Excel文件存储到Media文件夹 =======
    rev_file = request.FILES.get('excel')
    # 判断，是否有文件
    if not rev_file:
        return JsonResponse({'code': 0, 'msg': 'Excel文件不存在！'})
    # 获得一个唯一的名字： uuid +hash
    new_name = get_random_str()
    # 准备写入的URL
    file_path = os.path.join(settings.MEDIA_ROOT, new_name + os.path.splitext(rev_file.name)[1])
    # 开始写入到本次磁盘
    try:
        f = open(file_path, 'wb')
        # 多次写入
        for i in rev_file.chunks():
            f.write(i)
        # 要关闭
        f.close()
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': str(e)})

    #====== 2. 读取存储在Media文件夹的数据  =====
    ex_students = read_excel_dict(file_path)

    # ====3. 把读取的数据存储到数据库 =====
    # 定义几个变量： success:  error: errors
    success = 0
    error = 0
    error_snos = []

    # 开始遍历
    print(ex_students)
    for one_student in ex_students:
        try:

            obj_student = Student.objects.create(sno=one_student['sno'], sname=one_student['sname'], gender=one_student['gender'],
                                                      birthday=one_student['birthday'],mobile=one_student['mobile'],
                                                   email=one_student['email'],address=one_student['address'])
            # obj_student = Student(sno=one_student['sno'], sname=one_student['sname'], gender=one_student['gender'],
            #                                           birthday=one_student['birthday'],mobile=one_student['mobile'],
            #                                        email=one_student['email'],address=one_student['address'])
            # obj_student.save()
            # 计数
            success += 1
        except:
            # 如果失败了
            error += 1
            error_snos.append(one_student['sno'])


    # 4. 返回--导入信息（成功：5，失败：4--（sno））,所有学生
    obj_students = Student.objects.all().values()
    students = list(obj_students)
    return JsonResponse({'code':1, 'success':success,'error':error,'errors':error_snos, 'data':students})

def read_excel_dict(path:str):
    """读取Excel数据，存储为字典 --- [{},{},{},]"""
    # 实例化一个wrokbook
    workbook = openpyxl.load_workbook(path)
    # 实例化一个sheet
    sheet = workbook['student']
    # 定义一个变量存储最终的数据--[]
    students = []
    # 准备key
    keys = ['sno','sname','gender','birthday','mobile','email','address','image']
    # 遍历
    for row in sheet.rows:
        # 定义一个临时的字典
        temp_dict = {}
        # 组合值和key
        for index,cell in enumerate(row):
            # 组和
            temp_dict[keys[index]] = cell.value
        # 附加到list中
        students.append(temp_dict)
    #返回
    return students

def get_random_str():
    #获取uuid的随机数
    uuid_val = uuid.uuid4()
    #获取uuid的随机数字符串
    uuid_str = str(uuid_val).encode('utf-8')
    #获取md5实例
    md5 = hashlib.md5()
    #拿取uuid的md5摘要
    md5.update(uuid_str)
    #返回固定长度的字符串
    return md5.hexdigest()


def export_student_excel(request):
    """数据到excel"""
    # 获取所有的学生信息
    obj_students = Student.objects.all().values()
    # 转为List
    students = list(obj_students)
    # 准备名称
    excel_name = get_random_str() + ".xlsx"
    # 准备写入的路劲
    path = os.path.join(settings.MEDIA_ROOT, excel_name)
    # 写入到Excel
    write_to_excel(students, path)
    # 返回
    return JsonResponse({'code':1, 'name':excel_name })



def write_to_excel(data:list, path:str):
    """把数据库写入到Excel"""
    # 实例化一个workbook
    workbook = openpyxl.Workbook()
    # 激活一个sheet
    sheet = workbook.active
    # 为sheet命名
    sheet.title = 'student'
    # 准备keys
    keys = data[0].keys()
    # 准备写入数据
    for index, item in enumerate(data):
        # 遍历每一个元素
        for k,v in enumerate(keys):
            sheet.cell(row=index + 1, column=k+ 1, value=str(item[v]))
    # 写入到文件
    workbook.save(path)









